Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Run Stored-Procedure statement

The RUN STORED-PROC statement runs a RDBMS stored procedure or allows you to send Transact-SQL to a MS SQL Server based data source using an OpenEdge DataServer. It contains a procedure-name which is either the:

Name of the RDBMS stored procedure that you want to run.
Progress built-in procedure name, send–sql–statement, to send Transact-SQL to a MS SQL Server based data source.
Note: If your 4GL procedure is in a transaction block when a RUN STORED–PROCEDURE statement is executed, the RDBMS stored procedure runs within the same RDBMS transaction, even if your stored procedure declares its own transaction levels.

LOAD-RESULT-INTO phrase

The LOAD-RESULT-INTO function loads the result sets into a temp-table which is represented by a handle variable. Note that handle can also be defined as extent to enable you to pass more than one temp-table handle in those instances where SQL statement(s) are defined to return more than one result set.

When used with the RUN STORED-PROC statement or stored procedure to load result sets into Progress temp-tables, this function carries an implicit CLOSE-STORED PROCEDURE statement.

Note: The Progress compiler issues an error at runtime if the variable of type handle with the LOAD-RESULT-INTO function does not point to a temp-table.

For additional details about using the LOAD-RESULT-INTO phrase with the temp-table handle, see the "Loading result sets into temp-tables" section.

When used with the LOAD-RESULT-INTO phrase, the Temp-Table handle identifies the temp-table to which the result set will be loaded.

You can specify an array of one or more temp-table handle elements to retrieve stored procedure result sets and have the DataServer load the result set data directly into the associated temp-table(s). This approach allows you to have direct 4GL access to the fields defined in the temp-table.

The following types of temp-tables can support result sets:

Static — A temp-table whose schema is defined at compile time.
Dynamic — A temp-table whose schema is defined at run time. There are two types of dynamic temp-tables: dynamic-prepared and dynamic-unprepared.

For additional details about using the LOAD-RESULT-INTO phrase with the temp-table handle, see "Loading result sets into temp-tables" section.

PROC-STATUS phrase

The PROC-STATUS phase returns the return status from a MS SQL Server stored procedure. The return status is an integer value that typically indicates whether a stored procedure succeeded or failed; if it failed, a code indicating why it failed. See your SQL Server documentation for descriptions of the possible values for the return status.

PROC-HANDLE phrase

The PROC-HANDLE phrase allows you to specify a handle to act as a unique identifier for an MS SQL DataServer stored procedure. For example, the PROC-HANDLE assigns a value to the specified integer field or variable (integer–field) that uniquely identifies the stored procedure that is returning results from SQL Server.

Note these additional points about the PROC-HANDLE:

Progress Software Corporation recommends that you specify a procedure handle for each stored procedure that you run.
You do not have to specify a handle if there is only one active stored procedure and you do not include SQL statements in the OpenEdge application.

NO-ERROR option

The NO-ERROR option specifies that any ERROR condition that the RUN STORED–PROCEDURE statement produces is suppressed. Before you close a stored procedure, check the ERROR–STATUS handle for information on any errors that occurred. You receive an error when you attempt to close a stored procedure that did not start.

Note: This option must appear before any runtime parameter list.

PARAM phrase

The Param phrase identifies a run-time parameter to be passed to the stored procedure. A parameter has the following syntax:

Syntax

[( [INPUT|OUTPUT|INPUT-OUTPUT ] [ PARAM parameter-name = ] expression, ...      [INPUT|OUTPUT|INPUT-OUTPUT ][ PARAM parameter-name = ] expression )]

An expression is a constant, field name, variable name, or expression. INPUT is the default. OUTPUT and INPUT–OUTPUT parameters must be record fields or program variables.

Note: When you run send–sql–statement for a MS SQL-based data source, it passes a single character expression parameter containing the SQL statement you want the data source to execute.

If you do not specify parameter–name (the name of a keyword parameter defined by the stored procedure), you must supply all of the parameters in correct order. If you do specify parameter–name, you must precede your assignment statement with the keyword PARAM. If you do not supply a required parameter, and no default is specified in the stored procedure, you receive a run-time error.